﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DbFrame.Test
{
    using DbFrame;
    using DbFrame.BaseClass;
    using DbFrame.DbContext.Oracle;
    using DbFrame.DbContext.PostgreSql;
    using DbFrame.DbContext.SqlServer;
    using DbFrame.MySqlContext;
    using DbFrame.Test;
    using DbFrame.Test.Class;
    using DbFrame.Test.Models;
    using System.Collections.Concurrent;
    using System.ComponentModel;
    using System.Linq.Expressions;
    using System.Xml;

    class Program
    {

        static async Task Main(string[] args)
        {
            // var lng = (3951.790230 / 100.0).ToString("f7");//纬度;
            // var lat = (11615.400862 / 100.0).ToString("f7");//纬度;

            // Console.WriteLine($"lng:{lng},lat:{lat}");

            // var Key = "1";
            // ProgramCache.Add<Member>(Key, new Member()
            // {
            //     Member_Name = "hzy"
            // });

            // var _Member1 = ProgramCache.Get<Member>(Key);

            // _Member1.Member_Name = "wjf";

            // var _All = ProgramCache.GetAll();

            //Where<Member>(w => w.t1.Member_ID == Guid.Empty);

            // var arrr = Enum.GetNames(typeof(MethodName_Enum));



            //PostgreSQL
            //var postgreSql = new DbContextPostgreSQL("PostgreSQL Connection String");
            //var _MemberModel = postgreSql.Find<Member>(w => w.t1.Member_ID == Guid.Empty);
            //......




            //Oracel
            //var oracel = new DbContextOracle("Oracel Connection String");
            //oracel.Insert<int_tab>(new int_tab { name = "hzy" });
            //......





            //MySql
            //var mysqldb = new DbContextMySql("MySql Connection String");
            //var Sql = mysqldb.Query<Member>().Top(5).ToSql();
            //var sqlCode = Sql.Code.ToString();
            //sqlCode = mysqldb.Query<Member>().OrderByDesc(w => w.t1.Member_CreateTime).TakePage(1, 20).ToSql().Code.ToString();
            //......





            //SqlServer
            DbContextSqlServer.Register("Server=.;Database=AppDB;User ID=sa;Password=123456;MultipleActiveResultSets=true", (tabs) =>
            {
                //注册表                
                var _types = new List<Type>();
                _types.Add(typeof(Member));
                _types.Add(typeof(int_tab));
                _types.Add(typeof(Sys_User));

                //
                //注册 Model 并解析 xml 注解
                //
                foreach (var item in _types)
                {
                    tabs.Register(item, (propertyinfo, fielddescribe, tableType) =>
                    {
                        fielddescribe.DisplayName = Toolkit.ReadXmlSummary.XMLForMember(propertyinfo)?.InnerText?.Trim()?.Split("=>")?[0];
                    });
                }
            });

            var db = new DbContextSqlServer(PagingMode.ROW_NUMBER);

            var sql111 = db.Query<Member>().Distinct().OrderBy(w => w.t1.Member_CreateTime).TakePage(1, 20).ToSql();

            var a6 = db.QueryDataTable("select * from member");

            var mymen = db.Query<Member>().First();
            for (int i = 0; i < 100; i++)
            {
                await db.CommitAsync(async () =>
                {
                    await db.UpdateByIdAsync(mymen);
                });
            }

            var sql = db.Query<Member>()
                .GroupBy(w => w.t1.Member_Phone)
                .Having(w => w.t1.Member_Phone > 100)
                .Select(w => w.t1.Member_Phone).ToSql();

            db.Update<Member>(w => new Member
            {
                Member_Phone = w.Member_Phone + 10

            }, w => w.t1.Member_ID == Guid.Empty);

            //UNION 
            //var Sql1 = db.Query<Sys_Role>()
            //    //.OrderBy(w => w.SqlStr($"convert(int,{nameof(w.t1.Role_Num)})"))
            //    .Select(w => new { id = w.t1.Role_ID, SqlString = $"{nameof(w.t1.Role_Name)}+'(角色)' as [name] ", remark = w.t1.Role_Remark }).ToSql();

            //var Sql2 = db.Query<Sys_User>()
            //    .Select(w => new { id = w.t1.User_ID, SqlString = $"{nameof(w.t1.User_Name)}+'(用户)' as [name] ", remark = "" }).ToSql();

            //var Sql = Sql1.Union(Sql2);

            //var list = db.Query<Dictionary<string, object>>(Sql.Code.ToString(), Sql.GetDynamicParameters()).ToList();

            var name = "hzy777";
            var Member666 = db.Find<Member>(w => w.SqlStr<string>($"Lower({nameof(w.t1.Member_Name)})") == name.ToLower());

            //批量生成脚本并提交事务到数据库
            db.Commit((li) =>
            {
                for (int i = 0; i < 50; i++)
                {
                    db.InsertObject(new Member
                    {
                        Member_ID = Guid.NewGuid(),
                        Member_Name = $"Commit{i}"
                    }).Execute(li, out object KeyId);
                    Console.WriteLine($"KeyId={KeyId}");
                }
            });

            //异步
            //var _MemList = await db.Query<Member>().ToListAsync();

            var _mem = db.FindById<Member>(Guid.Parse("711f10eb-97df-4888-86c1-fd4854511d27"));
            _mem.Member_Name = "测试update555";
            //更新整个实体忽略字段 测试
            var update = db
                .UpdateObject(_mem)
                .Where(w => w.t1.Member_ID == _mem.Member_ID)
                .IgnoreCols(w => w.Member_ID)
                .IgnoreCols(w => w.Member_Name)
                .IgnoreCols(w => w.Member_UserID)
                .IgnoreCols(w => w.Member_CreateTime)
                .IgnoreCols(w => w.Member_Sex)
                .ToSql();
            //.Execute();

            var sss = db.Query<Member>(w => w.t1.Member_ID == Guid.Empty)
                .Select(w => new { state = "", 姓名 = 0, sqlstring = "123" })
                .ToSql();

            var str1 = " as a";
            var s = db.Query<Member>()
                .Where(w => (w.t1.Member_ID == Guid.Empty) | (w.t1.Member_Num == $"{2}"))
                .OrderBy(w => w.SqlStr("this is sql string"))
                .OrderBy(w => new { sql1 = $"num", w.t1.Member_Num, w.t1.Member_Name, w.t1.Member_Phone })
                .Select(w => "a" + str1)
                .ToSql();

            s = db.Query<Member>()
                .Where(w => w.SqlStr<int>($"{nameof(w.t1.Member_Num)}") == 1)
                .OrderBy(w => $"Member_Name{str1}")
                .Select(w => new { w.t1, w.t1.Member_CreateTime })
                .ToSql();
            db.Query<Member>().OrderBy(w => w.t1.Member_Name);

            var maxNum = db.Query<Member>().Max<int>(w => w.SqlStr<int>("convert(int,Member_Num)"));
            var maxDate = db.Query<Member>().Max(w => w.t1.Member_CreateTime);

            var x = new { user = "hzy", age = "24" };

            var str = "1,1,1,2".Replace("1", "2");

            Type t = x.GetType();

            var guids = new List<Guid>() { Guid.NewGuid(), Guid.NewGuid(), Guid.NewGuid() };

            var _Iquery = db.Query<Member>()
            .Where(w => w.In(w.t1.Member_ID, guids))// 语法 IN(1,2,3)
            .OrderByDesc(w => w.t1.Member_CreateTime);
            //.OrderBy(w => w.t1.Member_CreateTime);
            //.OrderBy(w => new { desc = w.t1.Member_CreateTime });//.Top(1).ToList();

            //分页
            var _Meber_List = _Iquery.TakePage(1, 20).ToList<dynamic>();

            //总数
            //var _Count = _Iquery.Count();

            //var _Max = _Iquery.Max(w => w.t1.Member_CreateTime);

            //var _Min = _Iquery.Min(w => w.t1.Member_CreateTime);

            //var _Sum = _Iquery.Sum(w => w.t1.Member_Phone);

            var _Member = db.Find<Member>(w => w.t1.Member_ID == Guid.Parse("9a604aa2-9ae6-4a2f-8ddb-d9e0289ead9e"));

            var test = "test";

            var query1 = db.Query<Member>()
            .Join<Sys_User>(w => w.t1.Member_UserID == w.t2.User_ID)
            .Where(w => w.t1.Member_Sex == "男")
            .Select(w => new
            {
                SqlString = test + "t1.Member_Name"
            })//new { w.t1.Member_Name, w.t2.User_LoginName })
            .ToList<dynamic>();

            var _Sql = db.Query<Member>()
            .Join<Sys_User>(w => w.t1.Member_UserID == w.t2.User_ID)
            .Where(w => w.t1.Member_Sex == "男")
            .Select(w => w.t1)//new { w.t1.Member_Name, w.t2.User_LoginName })
            .ToList<dynamic>();

            var _Member_First = db.Query<Member>()
            .Join<Sys_User>(w => w.t1.Member_UserID == w.t2.User_ID)
            .Where(w => w.t1.Member_Sex == "男")
            .Select(w => new { w.t1, w.t2.User_LoginName })
            .First<dynamic>();

            var _Member_First_0 = db.Query<Member>()
            .Join<Sys_User>(w => w.t1.Member_UserID == w.t2.User_ID)
            .Where(w => w.t1.Member_Sex == "男")
            .Select(w => w.t1.Member_Name)
            .First<dynamic>();

            var filed = "Member_Name";

            var _Member_First_1 = db.Query<Member>()
            .Join<Sys_User>(w => w.t1.Member_UserID == w.t2.User_ID)
            .Where(w => w.t1.Member_Sex == "男")
            .Select(w => filed)
            .First<dynamic>();

            foreach (var item in _Sql)
            {
                var a = item.Member_Name;
            }

            db.Commit(() =>
            {


                // int  自增
                //var _int_tab = new int_tab();
                //_int_tab.name = "hzy" + DateTime.Now;

                //_int_tab.id = db.Insert<int_tab>(_int_tab).ToInt32();

                /*** guid 主键 ***/

                for (int i = 0; i < 100; i++)
                {
                    _Member = new Member();
                    _Member.Member_Birthday = DateTime.Now;
                    _Member.Member_Introduce = "111";
                    _Member.Member_Name = "wjf" + DateTime.Now;
                    _Member.Member_Num = "1";
                    _Member.Member_Phone = 123123123;
                    _Member.Member_Sex = "男";
                    _Member.Member_ID = Guid.Parse(db.Insert<Member>(_Member).ToString());

                }

                var iquery = db.Query<Member>()
                                .Where(w => w.t1.Member_Sex.Contains("男"))
                                .Select(w => new { w.t1.Member_Name })
                                .ToList<dynamic>();

                var list = db.Query<Member>("select * from Member with(nolock)");

                throw new Exception("123");
            });

            //db.DeleteById<Member>(_Member.Member_ID);

        }

        public static void Where<T>(Expression<Func<HzyTuple<T>, bool>> where)
        {
            new WhereAnalysis().Visit(where);
            return;
        }

        public enum MethodName_Enum
        {
            StartsWith,
            Contains,
            EndsWith,
            Equals,
            SqlStr,
            In,
            NotIn
        }

    }

    public class WhereAnalysis : ExpressionVisitor
    {
        public override Expression Visit(Expression node)
        {
            return base.Visit(node);
        }

        protected override Expression VisitParameter(ParameterExpression node)
        {
            return base.VisitParameter(node);
        }

        protected override Expression VisitBinary(BinaryExpression node)
        {
            return this.VisitBinary(node);
        }

        protected override Expression VisitMember(MemberExpression node)
        {
            return this.VisitMember(node);
        }

        protected override Expression VisitConstant(ConstantExpression node)
        {
            return this.VisitConstant(node);
        }

        protected override Expression VisitUnary(UnaryExpression node)
        {
            return this.VisitUnary(node);
        }

        protected override Expression VisitMethodCall(MethodCallExpression node)
        {
            return this.VisitMethodCall(node);
        }

    }

}
